---- EXEC SP_PROGRAM_RELEASE_SCREEN_REPORT '0','PROGRAM','0','0','0','ISSUED','0' ,'0'
ALTER PROC SP_PROGRAM_RELEASE_SCREEN_REPORT

@PROCESSSTEP_ID VARCHAR(500),
@P_TYPE VARCHAR(50),
@DATE_FROM VARCHAR(50),
@DATE_TO VARCHAR(50),
@LOCATION_FROM VARCHAR(300),
@LOT_STATUS VARCHAR(100),
@DEFAULT_PROMPT VARCHAR(50),
@CUSTOMER_ID VARCHAR(50)

AS

BEGIN
	DECLARE @PART_NUMBER_BLANK VARCHAR(50)
	DECLARE	@LOT_NUMBER VARCHAR(50)
	DECLARE @PROCESS_TYPE VARCHAR(50)
	DECLARE @QTY_PULLED INT
	DECLARE @ORDER_QTY INT
	DECLARE @SHIPPED_QTY INT
	DECLARE @VENDOR_NAME VARCHAR(50)
	DECLARE @DATE_ISSUED DATETIME 
	DECLARE @RELEASE_NUMBER VARCHAR(50)
	DECLARE @PO_RELEASE VARCHAR(50)
	DECLARE @SALES_ORDER_NUMBER VARCHAR(50)
	DECLARE @LINE_ITEM_NO INT
	DECLARE @STATUS VARCHAR(50)
	DECLARE @RELEASE_PART_NUMBER VARCHAR(50)
	DECLARE @PS_TYPE_ID INT
	DECLARE @COOL_SHIPMENT_NO INT
	DECLARE @DESCRIPTION VARCHAR(200)
	DECLARE @CUSTOMER_NAME VARCHAR(65)
	DECLARE @PROG_RELEASE_ID VARCHAR(50)
	DECLARE @IS_PROG_RELEASE_CONTAINS_LOT INT
	DECLARE @REL_DATE_FROM DATETIME
	DECLARE	@REL_DATE_TO DATETIME
	
	DECLARE @MAX_REL_DATE DATETIME
	DECLARE	@MIN_REL_DATE DATETIME

	DECLARE @PROCESS_STEPS_TABLE TABLE( PROCESSSTEP_ID BIGINT )
	DECLARE @CUSTOMER_TABLE TABLE (CUSTOMER_ID VARCHAR(50))
	DECLARE @STATUS_TABLE TABLE ( STATUS VARCHAR(50))
	DECLARE @VENDOR_TABLE TABLE ( VENDOR_ID INT)
	DECLARE @SEARCH_RESULT_TABLE TABLE (PART_NUMBER_BLANK VARCHAR(50),LOT_NUMBER VARCHAR(50), PROCESS_TYPE VARCHAR(50), SHIPPED_QTY INT, ORDER_QTY INT , QTY_PULLED INT,VENDOR_NAME VARCHAR(50), DATE_ISSUED DATETIME, 
					RELEASE_NUMBER VARCHAR(50), PO_RELEASE VARCHAR(50),SALES_ORDER_NUMBER VARCHAR(50), LINE_ITEM_NO INT, STATUS VARCHAR(50), RELEASE_PART_NUMBER VARCHAR(50),PS_TYPE_ID INT,COOL_SHIPMENT_NO INT,PRODUCT_DESCRIPTION VARCHAR(200),CUSTOMER_NAME VARCHAR(65) )
	
	
	SELECT @MAX_REL_DATE = MAX( CONVERT( DATETIME, ISSUE_DATE )) FROM PROGRAM_RELEASE
	SELECT @MIN_REL_DATE = MIN( CONVERT( DATETIME, ISSUE_DATE )) FROM PROGRAM_RELEASE

	IF @LOCATION_FROM = '0'
	BEGIN
		INSERT INTO @VENDOR_TABLE (VENDOR_ID) SELECT DISTINCT VENDOR_ID FROM VENDOR 
	END
	ELSE
	BEGIN
		INSERT INTO @VENDOR_TABLE (VENDOR_ID) SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @LOCATION_FROM )
	END

	IF ( @DATE_FROM = '0') AND (@DATE_TO = '0') 
	BEGIN
		SET @REL_DATE_TO = @MAX_REL_DATE
		SET @REL_DATE_FROM = @MIN_REL_DATE
	END
	ELSE IF (@DATE_FROM <> '0') AND (@DATE_TO <> '0') 
	BEGIN
		SET @REL_DATE_TO = @DATE_TO
		SET @REL_DATE_FROM = @DATE_FROM
	END	
	ELSE IF (@DATE_FROM = '0') AND (@DATE_TO <> '0') 
	BEGIN
		SET @REL_DATE_TO = @DATE_TO
		SET @REL_DATE_FROM = @MIN_REL_DATE
	END 
 	ELSE IF (@DATE_FROM <> '0') AND (@DATE_TO = '0') 
	BEGIN
		SET @REL_DATE_TO = @MAX_REL_DATE
		SET @REL_DATE_FROM = @DATE_FROM	
	END
	ELSE
	BEGIN
		SET @REL_DATE_TO = @MAX_REL_DATE
		SET @REL_DATE_FROM = @MIN_REL_DATE
	END
	
	IF (@CUSTOMER_ID = '0') 
	BEGIN 
		INSERT INTO @CUSTOMER_TABLE ( CUSTOMER_ID ) SELECT CUSTOMER_ID FROM CUSTOMER 
	END
	ELSE
	BEGIN
		INSERT INTO @CUSTOMER_TABLE ( CUSTOMER_ID ) SELECT CUSTOMER_ID FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID
	END
	IF @LOT_STATUS = '0'
	BEGIN
		INSERT INTO @STATUS_TABLE (STATUS) SELECT DISTINCT STATUS FROM PROGRAM_RELEASE
		
	END
	ELSE
	BEGIN
		INSERT INTO @STATUS_TABLE (STATUS) SELECT * FROM FN_GET_STRINGS_IN_STRING(@LOT_STATUS)
	END
	IF( @P_TYPE = 'PROGRAM')
	BEGIN
		SET @P_TYPE = 'CLASS STORE' 
	END
	IF( @PROCESSSTEP_ID <> '0')
	BEGIN
		INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID )  SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PROCESSSTEP_ID )
	END
	ELSE IF (@PROCESSSTEP_ID = '0')
	BEGIN
		IF( @P_TYPE <> '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME = @P_TYPE  
		END
		ELSE IF( @P_TYPE = '0' )
		BEGIN
			INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID ) SELECT P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  
		END
	END
	DECLARE @RELEASECURSOR CURSOR
	DECLARE @LOTCURSOR CURSOR


	SET @RELEASECURSOR = CURSOR FOR
	SELECT PRO.PART_NUMBER ,PRO.P_COMMENTS,PS.PS_TYPE_NAME,VEN.V_NAME, ISSUE_DATE,OrderQty,PROG_RELEASE_NO, PO_NUMBER,
	SALESORDER_NO, COOL_LINE_ITEM_NO, STATUS, PR.PROCESSSTEP_ID,COOL_SHIPMENT_NO,C.C_NAME,PR.PROG_RELEASE_ID
	FROM PROGRAM_RELEASE PR , PRODUCT PRO , PROCESS_STEP PS, VENDOR VEN, CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO,CUSTOMER C--, LOT_TABLE LT
	WHERE PR.BLANK_PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PR.BLANK_PROCESSSTEP_ID IN  ( SELECT PROCESSSTEP_ID FROM @PROCESS_STEPS_TABLE ) 
	AND SO.SALESORDER_ID = PR.SALESORDER_ID AND SO.CUSTOMER_ID = C.CUSTOMER_ID AND COOL.LINEITEM_ID = PR.LINEITEM_ID AND PR.VENDOR_ID = VEN.VENDOR_ID AND  VEN.VENDOR_ID IN (SELECT VENDOR_ID FROM @VENDOR_TABLE)
	AND ISSUE_DATE <= CONVERT(DATETIME,@REL_DATE_TO) AND ISSUE_DATE >= CONVERT(DATETIME,@REL_DATE_FROM) AND C.CUSTOMER_ID IN ( SELECT CUSTOMER_ID FROM @CUSTOMER_TABLE )
	AND STATUS IN ( SELECT STATUS FROM @STATUS_TABLE )

	OPEN @RELEASECURSOR
	FETCH NEXT FROM @RELEASECURSOR INTO @PART_NUMBER_BLANK ,@DESCRIPTION, /*@LOT_NUMBER ,*/ @PROCESS_TYPE ,  @VENDOR_NAME , @DATE_ISSUED ,@ORDER_QTY , 
					@RELEASE_NUMBER , @PO_RELEASE ,@SALES_ORDER_NUMBER , @LINE_ITEM_NO , @STATUS , @RELEASE_PART_NUMBER,@COOL_SHIPMENT_NO,@CUSTOMER_NAME,@PROG_RELEASE_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @RELEASE_PART_NUMBER =  PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @RELEASE_PART_NUMBER
		
		SET @PS_TYPE_ID = 
		CASE 
			WHEN @PROCESS_TYPE ='FAB' THEN 1
			WHEN @PROCESS_TYPE ='WAFER STORE' THEN 2
			WHEN @PROCESS_TYPE ='SORT' THEN 3
			WHEN @PROCESS_TYPE ='DIE BANK' THEN 4
			WHEN @PROCESS_TYPE ='ASSEMBLY' THEN 5
			WHEN @PROCESS_TYPE ='TEST' THEN 6
			WHEN @PROCESS_TYPE ='CLASS STORE' THEN 7
			WHEN @PROCESS_TYPE ='MARK & PACK' OR @PROCESS_TYPE = 'MARK &AMP; PACK' THEN 8
			WHEN @PROCESS_TYPE ='FINISHED GOODS' OR @PROCESS_TYPE = 'FG' THEN 9
			ELSE 0
		END
		
		IF(@PROCESS_TYPE  = 'CLASS STORE')
		BEGIN
			SET @PROCESS_TYPE  = 'BLANK FGI'
		END
		
		--SET @DESCRIPTION = NULL
		--SELECT @DESCRIPTION = P_COMMENTS FROM PRODUCT WHERE PRODUCT_ID = ( SELECT TOP 1 PRODUCT_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO WHERE SO.SALESORDER_ID = COOL.SALESORDER_ID AND  SO.SALESORDER_NO = @SALES_ORDER_NUMBER AND  COOL_LINE_ITEM_NO = @LINE_ITEM_NO )
		IF ( @STATUS = 'ISSUED' /*OR @STATUS = 'CANCELLED'*/ )
		BEGIN
			
			INSERT INTO @SEARCH_RESULT_TABLE (PART_NUMBER_BLANK ,  PROCESS_TYPE ,  VENDOR_NAME , DATE_ISSUED , 
					RELEASE_NUMBER , PO_RELEASE , SALES_ORDER_NUMBER , LINE_ITEM_NO , STATUS , RELEASE_PART_NUMBER,PS_TYPE_ID,COOL_SHIPMENT_NO,PRODUCT_DESCRIPTION,CUSTOMER_NAME,ORDER_QTY)
			VALUES (@PART_NUMBER_BLANK , @PROCESS_TYPE ,  @VENDOR_NAME , @DATE_ISSUED , 
					@RELEASE_NUMBER , @PO_RELEASE ,@SALES_ORDER_NUMBER , @LINE_ITEM_NO , @STATUS , @RELEASE_PART_NUMBER,@PS_TYPE_ID,@COOL_SHIPMENT_NO,@DESCRIPTION,@CUSTOMER_NAME,@ORDER_QTY)

		END
		ELSE 
		BEGIN
			SET @IS_PROG_RELEASE_CONTAINS_LOT = 0
			SET @LOTCURSOR = CURSOR FOR  SELECT  LOT_NUMBER , QTY_PULLED, QTY_SHIPPED FROM PROG_RELEASE_LOT_TABLE PRLT , LOT_TABLE LT 
			WHERE PRLT.LOT_ID = LT.LOT_ID AND PROG_RELEASE_ID = @PROG_RELEASE_ID

			OPEN @LOTCURSOR
			FETCH NEXT FROM @LOTCURSOR INTO @LOT_NUMBER , @QTY_PULLED,@SHIPPED_QTY   

			WHILE @@FETCH_STATUS = 0
			BEGIN
				
				SET @IS_PROG_RELEASE_CONTAINS_LOT = @IS_PROG_RELEASE_CONTAINS_LOT + 1

				INSERT INTO @SEARCH_RESULT_TABLE (PART_NUMBER_BLANK , LOT_NUMBER , PROCESS_TYPE , SHIPPED_QTY , VENDOR_NAME , DATE_ISSUED , 
					RELEASE_NUMBER , PO_RELEASE , SALES_ORDER_NUMBER , LINE_ITEM_NO , STATUS , RELEASE_PART_NUMBER,PS_TYPE_ID,COOL_SHIPMENT_NO,PRODUCT_DESCRIPTION,CUSTOMER_NAME,ORDER_QTY,QTY_PULLED)
				VALUES (@PART_NUMBER_BLANK , @LOT_NUMBER , @PROCESS_TYPE , @SHIPPED_QTY , @VENDOR_NAME , @DATE_ISSUED , 
					@RELEASE_NUMBER , @PO_RELEASE ,@SALES_ORDER_NUMBER , @LINE_ITEM_NO , @STATUS , @RELEASE_PART_NUMBER,@PS_TYPE_ID,@COOL_SHIPMENT_NO,@DESCRIPTION,@CUSTOMER_NAME,@ORDER_QTY,@QTY_PULLED)

				SET @LOT_NUMBER  		= NULL
				SET @SHIPPED_QTY  		= NULL
				SET @QTY_PULLED			= NULL
						
			FETCH NEXT FROM @LOTCURSOR INTO @LOT_NUMBER , @QTY_PULLED,@SHIPPED_QTY  
 			
 			END
			CLOSE @LOTCURSOR
		 	DEALLOCATE @LOTCURSOR
			
			IF( @IS_PROG_RELEASE_CONTAINS_LOT = 0 )
			BEGIN
				INSERT INTO @SEARCH_RESULT_TABLE (PART_NUMBER_BLANK ,  PROCESS_TYPE ,  VENDOR_NAME , DATE_ISSUED , 
					RELEASE_NUMBER , PO_RELEASE , SALES_ORDER_NUMBER , LINE_ITEM_NO , STATUS , RELEASE_PART_NUMBER,PS_TYPE_ID,COOL_SHIPMENT_NO,PRODUCT_DESCRIPTION,CUSTOMER_NAME,ORDER_QTY,QTY_PULLED)
				VALUES (@PART_NUMBER_BLANK , @PROCESS_TYPE ,  @VENDOR_NAME , @DATE_ISSUED , 
					@RELEASE_NUMBER , @PO_RELEASE ,@SALES_ORDER_NUMBER , @LINE_ITEM_NO , @STATUS , @RELEASE_PART_NUMBER,@PS_TYPE_ID,@COOL_SHIPMENT_NO,@DESCRIPTION,@CUSTOMER_NAME,@ORDER_QTY,@QTY_PULLED)
 				
			END
		END	

		SET @LOT_NUMBER  		= NULL
		SET @SHIPPED_QTY  		= NULL
		SET @PART_NUMBER_BLANK 		= NULL
		SET @PROCESS_TYPE  		= NULL
		SET @VENDOR_NAME  		= NULL
		SET @DATE_ISSUED  		= NULL
		SET @RELEASE_NUMBER  		= NULL
		SET @PO_RELEASE  		= NULL
		SET @SALES_ORDER_NUMBER 	= NULL
		SET @LINE_ITEM_NO  		= NULL
		SET @STATUS  			= NULL
		SET @RELEASE_PART_NUMBER	= NULL
		SET @PS_TYPE_ID 		= NULL
		SET @COOL_SHIPMENT_NO 		= NULL
		SET @DESCRIPTION 		= NULL
		SET @CUSTOMER_NAME 		= NULL
		SET @ORDER_QTY			= NULL
		SET @PROG_RELEASE_ID 		= NULL

		FETCH NEXT FROM @RELEASECURSOR INTO @PART_NUMBER_BLANK ,@DESCRIPTION, /*@LOT_NUMBER ,*/ @PROCESS_TYPE , @VENDOR_NAME , @DATE_ISSUED , @ORDER_QTY , 
					@RELEASE_NUMBER , @PO_RELEASE ,@SALES_ORDER_NUMBER , @LINE_ITEM_NO , @STATUS , @RELEASE_PART_NUMBER,@COOL_SHIPMENT_NO,@CUSTOMER_NAME,@PROG_RELEASE_ID
	END
	CLOSE @RELEASECURSOR
 	DEALLOCATE @RELEASECURSOR
	
	SELECT * FROM @SEARCH_RESULT_TABLE ORDER BY PS_TYPE_ID,DATE_ISSUED DESC	
END
